IF EXISTS(SELECT
              *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[TUVAS].[sp_altaRol]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [TUVAS].[sp_altaRol]
GO

IF EXISTS(SELECT
              *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[TUVAS].[sp_altaFuncionalidadPorRol]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [TUVAS].[sp_altaFuncionalidadPorRol]
GO

IF EXISTS(SELECT
              *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[TUVAS].[sp_altaRolPorUsuario]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [TUVAS].[sp_altaRolPorUsuario]
GO

IF EXISTS(SELECT
              *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[TUVAS].[sp_altaCiudad]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [TUVAS].[sp_altaCiudad]
GO

IF EXISTS(SELECT
              *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[TUVAS].[sp_cargarCiudades]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [TUVAS].[sp_cargarCiudades]
GO

IF EXISTS(SELECT
              *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[TUVAS].[sp_cargarTservicios]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [TUVAS].[sp_cargarTservicios]
GO

IF EXISTS(SELECT
              *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[TUVAS].[sp_buscarServicioPorDescripcion]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [TUVAS].[sp_buscarServicioPorDescripcion]
GO

IF EXISTS(SELECT
              *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[TUVAS].[sp_buscarFuncionalidades]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [TUVAS].[sp_buscarFuncionalidades]
GO

IF EXISTS(SELECT
              *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[TUVAS].[sp_altaCliente]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [TUVAS].[sp_altaCliente]
GO

IF EXISTS(SELECT
              *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[TUVAS].[sp_altaMicro]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [TUVAS].[sp_altaMicro]
GO

IF EXISTS(SELECT
              *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[TUVAS].[sp_buscarIdCiudad]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [TUVAS].[sp_buscarIdCiudad]
GO

IF EXISTS(SELECT
              *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[TUVAS].[sp_buscarMarcaPorNombre]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [TUVAS].[sp_buscarMarcaPorNombre]
GO

IF EXISTS(SELECT
              *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[TUVAS].[sp_buscarUsuarioPorNombre]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [TUVAS].[sp_buscarUsuarioPorNombre]
GO

IF EXISTS(SELECT
              *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[TUVAS].[sp_buscarUsuario]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [TUVAS].[sp_buscarUsuario]
GO

IF EXISTS(SELECT
              *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[TUVAS].[sp_buscarMarcas]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [TUVAS].[sp_buscarMarcas]
GO

IF EXISTS(SELECT
              *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[TUVAS].[sp_altaMarca]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [TUVAS].[sp_altaMarca]
GO

IF EXISTS(SELECT
              *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[TUVAS].[sp_altaServicio]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [TUVAS].[sp_altaServicio]
GO

IF EXISTS(SELECT
              *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[TUVAS].[sp_altaRecorrido]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [TUVAS].[sp_altaRecorrido]
GO

IF EXISTS(SELECT
              *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[TUVAS].[sp_altaButacaUbicacion]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [TUVAS].[sp_altaButacaUbicacion]
GO

IF EXISTS(SELECT
              *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[TUVAS].[sp_altaButaca]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [TUVAS].[sp_altaButaca]
GO

IF EXISTS(SELECT
              *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[TUVAS].[sp_altaViaje]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [TUVAS].[sp_altaViaje]
GO

IF EXISTS(SELECT
              *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[TUVAS].[sp_altaVenta]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [TUVAS].[sp_altaVenta]
GO

IF EXISTS(SELECT
              *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[TUVAS].[sp_altaProductoVendido]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [TUVAS].[sp_altaProductoVendido]
GO

IF OBJECT_ID('TUVAS.PRODUCTO_VENDIDO.tg_altaPunto', 'TR') IS NOT NULL
    DROP TRIGGER TUVAS.tg_altaPunto;
GO

--	---------------------------------------------------------------------------------
--	---------------------------------------------------------------------------------
--	---------------------------------------------------------------------------------

-- Alta de rol
CREATE PROCEDURE TUVAS.sp_altaRol
        @nombre NVARCHAR(50)
AS
    SET NOCOUNT ON
    BEGIN
        IF NOT exists(SELECT
                          *
                      FROM TUVAS.ROL
                      WHERE nombre = @nombre)
            BEGIN
                INSERT INTO TUVAS.ROL (NOMBRE, ESTADO)
                    VALUES (@nombre, 1)
            END
    END
GO

-- Alta de funcionalidad por rol
CREATE PROCEDURE TUVAS.sp_altaFuncionalidadPorRol
        @rolId           SMALLINT,
        @funcionalidadId INT
AS
    SET NOCOUNT ON
    BEGIN
        INSERT INTO TUVAS.FUNCIONALIDAD_X_ROL VALUES (@rolId, @funcionalidadId);
    END
GO

-- Alta de roles por usuario
CREATE PROCEDURE TUVAS.sp_altaRolPorUsuario
        @usuarioId INT,
        @rolId     SMALLINT
AS
    SET NOCOUNT ON
    BEGIN
        INSERT INTO TUVAS.ROLES_X_USUARIO VALUES (@usuarioId, @rolId);
    END
GO

-- Buscar ciudad por nombre
CREATE PROCEDURE TUVAS.sp_altaCiudad
          @nombreCiudad NVARCHAR(255)
        , @id           BIGINT OUTPUT
AS
    BEGIN
        SELECT
            @id = ID
        FROM TUVAS.CIUDAD
        WHERE NOMBRE = @nombreCiudad

        IF (@id IS null)
            BEGIN
                INSERT INTO TUVAS.CIUDAD VALUES (@nombreCiudad)

                SELECT
                    @id = @@IDENTITY
            END
    END
GO

-- TODO : sp_cargarCiudades
CREATE PROCEDURE TUVAS.sp_cargarCiudades
AS
    SET NOCOUNT ON
    BEGIN
        SELECT
            ID
            , NOMBRE
        FROM TUVAS.CIUDAD
        ORDER BY NOMBRE
    END
GO

-- TODO : sp_cargarTservicios
CREATE PROCEDURE TUVAS.sp_cargarTservicios
AS
    SET NOCOUNT ON
    BEGIN
        SELECT
            ID
            , DESCRIPCION
        FROM TUVAS.TIPO_SERVICIO
        ORDER BY ID
    END
GO


-- Buscar servicio por descripcion
CREATE PROCEDURE TUVAS.sp_buscarServicioPorDescripcion
          @descripcion NVARCHAR(255)
        , @id          SMALLINT OUTPUT
AS
    BEGIN
        SELECT
            @id = ID
        FROM TUVAS.TIPO_SERVICIO
        WHERE DESCRIPCION = @descripcion
    END
GO

-- Busco todas las funcionalidades
CREATE PROCEDURE TUVAS.sp_buscarFuncionalidades
AS
    BEGIN
        SELECT
            ID
            , DESCRIPCION
        FROM TUVAS.FUNCIONALIDAD
        ORDER BY DESCRIPCION
    END
GO

--	Creo el cliente
CREATE PROCEDURE TUVAS.sp_altaCliente
          @nombre          NVARCHAR(255)
        , @apellido        NVARCHAR(255)
        , @identificacion  NUMERIC(18, 0)
        , @direccion       NVARCHAR(255)
        , @telefono        NUMERIC(18, 0)
        , @fechaNacimiento DATETIME
        , @sexo            SMALLINT
        , @discapacitado   BIT
        , @jubilado        BIT
        , @pensionado      BIT
        , @mail            NVARCHAR(255)
AS
    SET NOCOUNT ON
    IF NOT exists(SELECT
                      identificacion
                  FROM TUVAS.CLIENTE
                  WHERE identificacion = @identificacion)
        BEGIN
            INSERT INTO TUVAS.CLIENTE
                VALUES (@identificacion
                    , @nombre
                    , @apellido
                    , @direccion
                    , @telefono
                    , @fechaNacimiento
                    , @sexo
                    , @discapacitado
                    , @jubilado
                    , @pensionado
                    , @mail)
        END
GO

--	Creo el micro
CREATE PROCEDURE TUVAS.sp_altaMicro
          @patente        NVARCHAR(255)
        , @modelo         NVARCHAR(255)
        , @marcaId        INT
        , @tipoServicioId SMALLINT
        , @pesoDisponible NUMERIC(18, 0)

AS

    BEGIN
        IF NOT exists(SELECT
                          *
                      FROM TUVAS.MICRO
                      WHERE PATENTE = @patente)
            BEGIN
                INSERT INTO TUVAS.MICRO
                ([PATENTE]
                    , [MODELO]
                    , [MARCA]
                    , [TIPO_SERVICIO]
                    , [PESO_DISPONIBLE]
                    , [FECHA_ALTA]
                    , [ESTADO]
                    , [FECHA_BAJA])

                    VALUES (
                        @patente
                        , @modelo
                        , @marcaId
                        , @tipoServicioId
                        , @pesoDisponible
                        , getdate()
                        , 1
                        , null)
            END
    END
GO


-- Buscar ID de Ciudad.
CREATE PROCEDURE TUVAS.sp_buscarIdCiudad
          @nombre NVARCHAR(255)
        , @id     SMALLINT OUTPUT
AS
    BEGIN
        SELECT
            @id = ID
        FROM TUVAS.CIUDAD
        WHERE NOMBRE = @nombre
    END
GO


-- Buscar marca por nombre
CREATE PROCEDURE TUVAS.sp_buscarMarcaPorNombre
          @nombre NVARCHAR(255)
        , @id     SMALLINT OUTPUT
AS
    BEGIN
        SELECT
            @id = ID
        FROM TUVAS.MICRO_MARCA
        WHERE NOMBRE = @nombre
    END
GO

-- Buscar usuario por nombre
CREATE PROCEDURE [TUVAS].[sp_buscarUsuarioPorNombre]
          @usuario     NVARCHAR(255)
        , @contrasenia NVARCHAR(255)
        , @mensaje     SMALLINT OUTPUT

AS
    BEGIN

        IF EXISTS(SELECT 1
                  FROM TUVAS.USUARIO
                  WHERE NOMBRE_USUARIO = @usuario)
            BEGIN

                SELECT @mensaje = LOGIN_FALLIDOS
                FROM TUVAS.USUARIO
                WHERE NOMBRE_USUARIO = @usuario

                SET @mensaje = @mensaje + 1
                UPDATE TUVAS.USUARIO
                SET LOGIN_FALLIDOS = @mensaje
                WHERE NOMBRE_USUARIO = @usuario

                IF EXISTS(SELECT 1
                          FROM TUVAS.USUARIO
                          WHERE NOMBRE_USUARIO = @usuario AND CONTRASENIA = @contrasenia)
                    BEGIN
                        SET @mensaje = 0
                        UPDATE TUVAS.USUARIO
                        SET LOGIN_FALLIDOS = @mensaje
                        WHERE NOMBRE_USUARIO = @usuario
                        RETURN @mensaje
                    END
                ELSE
                    BEGIN
                        IF ((
                                SELECT LOGIN_FALLIDOS
                                FROM TUVAS.USUARIO
                                WHERE NOMBRE_USUARIO = @usuario) < 4)
                            BEGIN
                                RETURN @mensaje
                            END
                        ELSE
                            BEGIN
                                SET @mensaje = 0
                                UPDATE TUVAS.USUARIO
                                SET LOGIN_FALLIDOS = @mensaje
                                WHERE NOMBRE_USUARIO = @usuario

                                SET @mensaje = 99
                                RETURN @mensaje
                            END

                    END
            END
        ELSE
            BEGIN
                SET @mensaje = -1
                RETURN @mensaje
            END
    END
GO

-- Buscar usuarios
CREATE PROCEDURE [TUVAS].[sp_buscarUsuario]
AS
    BEGIN
        SET NOCOUNT ON;
        SELECT
            *
        FROM TUVAS.USUARIO
    END
GO

-- Cargar marcas de micros
CREATE PROCEDURE TUVAS.sp_buscarMarcas
AS
    SET NOCOUNT ON
    BEGIN
        SELECT
            ID
            , NOMBRE
        FROM TUVAS.MICRO_MARCA
        ORDER BY NOMBRE
    END
GO

-- Inserta la marca que le pasamos siempre y cuando no exista. Exista o no, retorna el ID de la marca, ya sea la generada o la existente.
CREATE PROCEDURE TUVAS.sp_altaMarca
          @nombre NVARCHAR(255)
        , @id     INT OUTPUT
AS
    BEGIN
        SELECT
            @id = ID
        FROM TUVAS.MICRO_MARCA
        WHERE NOMBRE = @nombre

        IF (@id IS null)
            BEGIN
                INSERT INTO TUVAS.MICRO_MARCA
                (NOMBRE)
                    VALUES (@nombre)

                SELECT
                    @id = @@IDENTITY
            END
    END
GO

-- Inserta el tipo de servicio que le pasamos siempre y cuando no exista. Exista o no, retorna el ID del tipo de servicio, ya sea el generado o el existente.
CREATE PROCEDURE TUVAS.sp_altaServicio
          @descripcion NVARCHAR(255)
        , @id          SMALLINT OUTPUT
AS
    BEGIN
        SELECT
            @id = ID
        FROM TUVAS.TIPO_SERVICIO
        WHERE DESCRIPCION = @descripcion

        IF (@id IS null)
            BEGIN
                INSERT INTO TUVAS.TIPO_SERVICIO
                (DESCRIPCION)
                    VALUES (@descripcion)

                SELECT
                    @id = @@IDENTITY
            END
    END

GO

-- Inserta el recorrido siempre y cuando no exista, y además valida que las ciudades no sean las mismas.
-- Exista o no, retorna el ID del recorrido, ya sea el generado o el existente.
CREATE PROCEDURE TUVAS.sp_altaRecorrido
          @codigo           NVARCHAR(50)
        , @ciudadOrigenId   BIGINT
        , @ciudadDestinoId  BIGINT
        , @precioBaseKg     NUMERIC(18, 2)
        , @precioBasePasaje NUMERIC(18, 2)
        , @tipoServicioId   SMALLINT
        , @id               INT OUTPUT

AS
    BEGIN
        IF (@ciudadOrigenId != @ciudadDestinoId)
            BEGIN

                SELECT
                    @id = ID
                FROM TUVAS.RECORRIDO
                WHERE CODIGO = @codigo

                IF (@id IS null)
                    BEGIN
                        INSERT INTO TUVAS.RECORRIDO
                        ([CODIGO]
                            , [ORIGEN]
                            , [DESTINO]
                            , [PRECIO_BASE_KG]
                            , [PRECIO_BASE_PASAJE]
                            , [TIPO_SERVICIO]
                            , [ELIMINADO])
                            VALUES (@codigo
                                , @ciudadOrigenId
                                , @ciudadDestinoId
                                , @precioBaseKg
                                , @precioBasePasaje
                                , @tipoServicioId
                                , 0)

                        SELECT
                            @id = @@IDENTITY
                    END
            END
    END
GO

-- Inserta una ubicación para una butaca.
CREATE PROCEDURE TUVAS.sp_altaButacaUbicacion
          @descripcion NVARCHAR(255)
        , @id          SMALLINT OUTPUT
AS
    BEGIN
        SELECT
            @id = ID
        FROM TUVAS.BUTACA_UBICACION
        WHERE DESCRIPCION = @descripcion

        IF (@id IS null)
            BEGIN
                INSERT INTO [TUVAS].[BUTACA_UBICACION]
                (DESCRIPCION)
                    VALUES (@descripcion)

                SELECT
                    @id = @@IDENTITY
            END
    END
GO

-- Inserta una butaca
CREATE PROCEDURE TUVAS.sp_altaButaca
          @numero      NUMERIC(18, 0)
        , @piso        NUMERIC(18, 0)
        , @ubicacionId SMALLINT
        , @microId     NVARCHAR(50)
        , @id          NUMERIC(18, 0) OUTPUT
AS
    BEGIN
        SELECT
            @id = butaca.ID
        FROM TUVAS.BUTACA BUTACA, TUVAS.BUTACAS_X_MICRO BUTXMIC
        WHERE (butaca.NUMERO = @numero
               AND butaca.PISO = @piso
               AND butaca.UBICACION = @ubicacionId)
              AND butxmic.BUTACA_ID = butaca.ID
              AND butxmic.MICRO_PATENTE = @microId

        IF (@id IS null)
            BEGIN
                INSERT INTO [TUVAS].[BUTACA]
                (NUMERO, UBICACION, PISO)
                    VALUES (@numero, @ubicacionId, @piso)

                SELECT
                    @id = @@IDENTITY

                INSERT INTO TUVAS.BUTACAS_X_MICRO ([MICRO_PATENTE], [BUTACA_ID]) VALUES (@microId, @id)
            END
    END
GO

-- Crea un viaje, siempre y cuando la fecha de salida sea mayor a la fecha actual, la de estimada mayor a la de salida, si ya no existe en la BD,
-- y si los tipos de servicio del recorrido y del micro seleccionados son iguales.
CREATE PROCEDURE TUVAS.sp_altaViaje
          @recorridoId          INT
        , @microId              NVARCHAR(50)
        , @fechaSalida          DATETIME
        , @fechaLlegada         DATETIME
        , @fechaLlegadaEstimada DATETIME
        , @id                   BIGINT OUTPUT
AS
    IF (@fechaSalida > getdate()
        AND @fechaLlegadaEstimada > @fechaSalida
        AND (
                SELECT
                    TIPO_SERVICIO
                FROM TUVAS.MICRO
                WHERE PATENTE = @microId) = (
                SELECT
                    TIPO_SERVICIO
                FROM TUVAS.RECORRIDO
                WHERE ID = @recorridoId))
        BEGIN
            SELECT
                @id = ID
            FROM TUVAS.VIAJE
            WHERE RECORRIDO = @recorridoId
                  AND MICRO = @microId
                  AND FECHA_SALIDA = @fechaSalida
                  AND FECHA_LLEGADA = @fechaLlegada

--         TODO : validar también si el micro no tiene viajes para esa fecha, creo que con sacar el filtro por recorrido alcanza

            IF (@id IS null)
                BEGIN
                    INSERT INTO [TUVAS].[VIAJE]
                    (RECORRIDO, MICRO, FECHA_SALIDA, FECHA_LLEGADA, FECHA_LLEGADA_ESTIMADA)
                        VALUES (@recorridoId, @microId, @fechaSalida, @fechaLlegada, @fechaLlegadaEstimada)

                    SELECT
                        @id = @@IDENTITY
                END
        END
GO


-- Da de alta una nueva venta. Si como fecha pasamos null, entonces tomara la fecha actual.
CREATE PROCEDURE TUVAS.sp_altaVenta
          @fecha     DATETIME
        , @clienteId NUMERIC(18, 0)
        , @monto     NUMERIC(18, 2)
        , @id        NUMERIC(18, 0) OUTPUT
AS
    BEGIN
        INSERT INTO [TUVAS].[VENTA]
        ([FECHA_COMPRA], [CLIENTE], [MONTO])
            VALUES (isnull(@fecha, getdate()), @clienteId, @monto)

        SELECT
            @id = @@IDENTITY
    END
GO


-- Da de alta un nuevo producto vendido (ya sea pasaje o paquete/encomienda)
CREATE PROCEDURE TUVAS.sp_altaProductoVendido
          @id           NUMERIC(18, 0)
        , @tipoProducto SMALLINT
        , @ventaId      NUMERIC(18, 0)
        , @precio       NUMERIC(18, 2)
        , @clienteId    NUMERIC(18, 0)
        , @butacaId     NUMERIC(18, 0)
        , @pesoPaquete  NUMERIC(18, 0)
AS
    SET NOCOUNT ON
    BEGIN
        INSERT INTO [GD1C2013].[TUVAS].[PRODUCTO_VENDIDO]
        ([ID], [TIPO_PRODUCTO], [VENTA], [PRECIO], [CLIENTE], [BUTACA], [PESO], [DEVOLUCION])
            VALUES
            (@id, @tipoProducto, @ventaId, @precio, @clienteId, @butacaId, @pesoPaquete, null)
    END
GO


-- Trigger que da de alta N puntos para un cliente determinado luego de haber creado un registro en TUVAS.PRODUCTO_VENDIDO.
CREATE TRIGGER tg_altaPunto
ON TUVAS.PRODUCTO_VENDIDO
AFTER INSERT
AS
    BEGIN
        DECLARE @monto   NUMERIC(18, 0)
        SET @monto = (
            SELECT
                TOP 1 cast(ROUND(PRECIO / 5, 0, 1) AS INT)
            FROM inserted)

        DECLARE @cliente NUMERIC(18, 0)
        SET @cliente = (
            SELECT TOP 1 CLIENTE
            FROM inserted)

        INSERT INTO [GD1C2013].[TUVAS].[CLIENTE_PUNTOS]
        ([CLIENTE]
            , [FECHA_ALTA]
            , [CANTIDAD])
            VALUES (
                @cliente
                , getdate()
                , @monto)
    END
GO
